/**@file fml_sqlite3.c
 * @note 
 * @brief  数据库接口层
 * 
 * @author   luoxi
 * @date     20240228
 * @version  V1.0.0
 * 
 * @note Description here 
 * @note History:        
 * @note     <author>   <time>    <version >   <desc>
 * @note  
 * @warning  
 */

#include "sqlite3.h"
#include "fml_sqlite3.h"
#include "log_level.h"

/* 数据库初始化 */
sqlite3 *db = NULL;

student_t stu_base =
{
    .id = 20240301,
    .name = "tang",
    .score = 98,  
};

int fml_sqlite3_creat_table(sqlite3 *db);                  /* 创建   创建一张表 */
int fml_sqlite3_insert(sqlite3 *db, student_t stu_base);   /* 增   插入一条数据 */
int fml_sqlite3_delete(sqlite3 *db, student_t stu_base);   /* 删   删除一条数据 */
int fml_sqlite3_update(sqlite3 *db, student_t stu_base);   /* 改   修改一条数据 */
int fml_sqlite3_query(sqlite3 *db);                        /* 打印   打印数据库数据 */

/**
 * @brief 数据库初始化
 * 
 * @param[in] 1 数据库句柄 
 * @return 数据库是否初始化成功  -1 不成功 0 成功
 */
int fml_sqlite3_init()
{
    int db_exist_flag = 0;
    /* 数据库测试 */
	if (access(DATEBASE, F_OK) == 0)
	{
	    db_exist_flag = 1;
		log_custom("db_exist_flag = %d", db_exist_flag);
	}
	else
	{
	    db_exist_flag = 0;
		log_custom("db_exist_flag = %d", db_exist_flag);
	}

    int resultdb;
    resultdb = sqlite3_open(DATEBASE, &db);
    if(resultdb != SQLITE_OK)
    {
        log_custom("Create/Open test.db error!");
        return -1;
    }
    log_custom("Create/Open test.db success!");

	if(db_exist_flag == 0)
	{
	    fml_sqlite3_creat_table(db);

		stu_base.id = 20240101;
		stu_base.name = "song";
		stu_base.score = 80;
		fml_sqlite3_insert(db, stu_base);

		stu_base.id = 20240201;
		stu_base.name = "yuan";
		stu_base.score = 70;
		fml_sqlite3_insert(db, stu_base);
		fml_sqlite3_query(db);	
	}

    fml_sqlite3_close(db);

    return 0;
}

/**
 * @brief 关闭数据库
 * 
 * @param[in] 1 数据库句柄 
 * @return 数据库是否关闭成功  -1 不成功 0 成功
 */
int fml_sqlite3_close(sqlite3 *db)
{
    sqlite3_close(db);
    return 0;
}

/**
 * @brief 数据库创建一张名为class的表
 * 
 * @param[in] 1 数据库句柄 
 * @return 数据库是否初始化成功  -1 不成功 0 成功
 */
int fml_sqlite3_creat_table(sqlite3 *db)
{
    int ret = -1;
    char* errmsg = NULL;
    char sql[128] = {0};
    sprintf(sql, "create table if not exists class(id Integer,name char,score Integer);");
    ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
    if(SQLITE_OK != ret)
    {
        log_error("sqlite3 exec creat_table error:%s", errmsg);
        log_error("sqlite3 exec creat_table ret = %d", ret);
    }
    else 
    {
        log_custom("creat_table success!");
    }
    return ret;
}

/**
 * @brief 增   插入一条数据
 * 
 * @param[in] 1 数据库句柄 
 * @return 插入数据是否初始化成功  -1 不成功 0 成功
 */
int fml_sqlite3_insert(sqlite3 *db, student_t stu_base)
{
    int ret = -1;
    char* errmsg = NULL;
    char sql[128] = {0};
    sprintf(sql, "insert into class values(%d, '%s' ,%d);", stu_base.id, stu_base.name, stu_base.score);
    ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
    if(SQLITE_OK != ret)
    {
        log_error("sqlite3 exec insert error:%s", errmsg);
    }
    else 
    {
        log_custom("insert success!");
    }
    return ret;
}

/**
 * @brief 删  删除一条数据
 * 
 * @param[in] 1 数据库句柄 
 * @return 删除数据是否成功  -1 不成功 0 成功
 */
int fml_sqlite3_delete(sqlite3 *db, student_t stu_base)
{
    int ret = -1;
    char* errmsg = NULL;
    char sql[128] = {0};
    sprintf(sql, "delete from class where id=%d;", stu_base.id);
    ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
    if(SQLITE_OK != ret)
    {
        log_error("sqlite3 exec delete error:%s", errmsg);
    }
    else 
    {
        log_custom("delete success!");
    }
    return ret;
}

/**
 * @brief 改 修改一条数据
 * 
 * @param[in] 1 数据库句柄 
 * @return 修改数据是否成功  -1 不成功 0 成功
 */
int fml_sqlite3_update(sqlite3 *db, student_t stu_base)
{
    int ret = -1;
    char* errmsg = NULL;
    char sql[128] = {0};
    sprintf(sql, "update class set score=%d where id=%d;", stu_base.score, stu_base.id);
    ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
    if(SQLITE_OK != ret)
    {
        log_error("sqlite3 exec update error:%s", errmsg);
    }
    else 
    {
        log_custom("update success!");
    }
    return ret;
}

/**
 * @brief 查找数据处理
 * 
 * @param[in] 
 * @return 
 */
int query_fn(void *params, int count, char ** valve, char ** name)
{
    
    for(int i = 0; i < count; i++)
    {
        log_custom("%-11s", valve[i]);
    }
    log_custom("\n");
    return 0;

}

/**
 * @brief 查 查找一条数据
 * 
 * @param[in] 1 数据库句柄 
 * @return 查找数据是否成功  -1 不成功 0 成功
 */
int fml_sqlite3_query(sqlite3 *db)
{
    int ret = -1;
    char* errmsg = NULL;
    char sql[128] = {0};
    sprintf(sql, "select * from class;");
    ret = sqlite3_exec(db, sql, query_fn, NULL, &errmsg);
    if(SQLITE_OK != ret)
    {
        log_error("sqlite3 exec update error:%s", errmsg);
    }
    else 
    {
        log_custom("update success!");
    }
    return ret;
}